package ro.gateway.aida.fnd;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import ro.gateway.aida.admin.PropertyListDB;
import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.Person;
import ro.gateway.aida.obj.db.AIDAActivityObjectDB;
import ro.gateway.aida.obj.db.OrganizationDB;
import ro.gateway.aida.srv.ICFService;
import ro.gateway.aida.srv.IIDGenerator;


/**
 * <p>Title: Romanian AIDA</p> <p>Description: </p> <p>Copyright: Copyright (comparator) 2003</p> <p>Company:
 * ro-gateway</p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: FndPersMan.java,v 1.1 2004/10/24 23:37:14 mihaipostelnicu Exp $
 */
public class FndPersMan extends DBPersistenceManager {
	private FndPersMan(PersistenceToken _token) {
		super(_token);
	}

	public static FndPersMan getManager(PersistenceToken token) {
		return new FndPersMan(token);
	}

	public final void insert(FundingItem item)
		 throws SQLException {

		IIDGenerator idGenerators = db_token.getIdgenerators();

		AIDAActivityObjectDB.getManager(token).insert(idGenerators, item);
		Connection con = getConnection();

		try {
//			"INSERT INTO funding (id, org_id, amount, harm_currency, status, comment, typeassist, termassist, signdate) " +
//			"VALUES (?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(INSERT_FND_ITEM);
			ps.setLong(1, item.getId());
			long org_id = item.getOrg_id();
			if (org_id == -1) {
				ps.setNull(2, Types.INTEGER);
			} else {
				ps.setLong(2, org_id);
			}
			ps.setDouble(3, item.getAmount());
			ps.setString(4, item.getCurrency());
			ps.setInt(5, (int) item.getStatus_id());
			ps.setString(6, item.getComments());
			if (item.getType_of_assistance() != null) {
				ps.setLong(7, item.getType_of_assistance().getId());
			} else {
				ps.setNull(7, Types.INTEGER);
			}
			if (item.getTerms_of_assistance() != null) {
				ps.setLong(8, item.getTerms_of_assistance().getId());
			} else {
				ps.setNull(8, Types.INTEGER);
			}
			ps.setDate(9, new Date(item.getSignature_date()));
			ps.executeUpdate();
			ps.close();

			// persons
//			ArrayList persons = item.getPersons();
//			if (persons != null && persons.size() > 0) {
//				ps = con.prepareStatement(INSERT_PERS);
//
//				for (int i = 0; i < persons.size(); i++) {
//					Person p = (Person) persons.get(i);
//					ps.clearParameters();
//					ps.setLong(1, item.getId());
//					ps.setLong(2, p.getId());
//					ps.setLong(3, item.getActivityId());
//					ps.executeUpdate();
//				}
//
//				ps.close();
//			}

			insertTable(item);
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}


	public final FundingItem[] getFundingsByActivity(long activity_id)
		 throws SQLException {

		Connection con = getConnection();
		FundingItem[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_FUNDINGS_BY_ACT);
			ps.setLong(1, activity_id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingItem item = new FundingItem();
//				"SELECT f.id, f.org_id, f.amount, f.harm_currency, f.status, f.comment, f.typeassist, f.termassist, f.signdate " +
//				"FROM funding f, objects o " +
//				"WHERE o.activity_id = ? AND f.id = o.id";
				item.setId(rs.getLong(1));
				item.setOrg(OrganizationDB.getManager(token).getByID(rs.getLong(2)));
				item.setAmount(rs.getDouble(3));
				item.setCurrency(rs.getString(4));
				item.setStatus(PropertyListDB.getManager(db_token).getItemById(rs.getLong(5)));
//				item.setStatus(FundingStatusPersMan.getManager(db_token).getById(rs.getLong(5)));
				item.setComments(rs.getString(6));
				item.setType_of_assistance(PropertyListDB.getManager(db_token).getItemById(rs.getLong(7)));
				item.setTerms_of_assistance(PropertyListDB.getManager(db_token).getItemById(rs.getLong(8)));
				java.sql.Date date = rs.getDate(9);
				if (date != null) {
					item.setSignature_date(date.getTime());
				} else {
					item.setSignature_date(new Date(System.currentTimeMillis()).getTime());
				}
				getTable(item);

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		con = getConnection();
		// persons
		ArrayList persons = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_PERS_FOR_ACT);
			ps.setLong(1, activity_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				long[] ids = new long[2];
				ids[0] = rs.getLong(1); // funding_id
				ids[1] = rs.getLong(2); // person_id
				persons.add(ids);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingItem[items.size()];
			items.toArray(result);

			for (int i = 0; i < result.length; i++) {
				ArrayList fnd_persons = new ArrayList();
				long[] pers_ids = extract_pers_ids(persons, result[i].getId());
				if (pers_ids != null && pers_ids.length > 0) {
					for (int j = 0; j < pers_ids.length; j++) {
						fnd_persons.add(getPersonByID(pers_ids[j]));
					}
				}
				result[i].setPersons(fnd_persons);
			}
		}

		return result;
	}

	private long[] extract_pers_ids(ArrayList all_persons,
							  long fnd_id) {
		long[] result = new long[0];
		for (int i = 0; i < all_persons.size(); i++) {
			long[] ids = (long[]) all_persons.get(i);
			if (ids[0] == fnd_id) {
				long[] new_result = new long[result.length + 1];
				System.arraycopy(result, 0, new_result, 0, result.length);
				new_result[result.length] = ids[1];
				result = null;
				result = new_result;
			}
		}

		return result;
	}

	public final FundingItem[] getFundingsIDSByActivity(long activity_id)
		 throws SQLException {
		ICFService cfService = db_token.getCFService();
		String poolName = db_token.getPool_name();

		Connection con = cfService.getConnection(poolName);
		FundingItem[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_FUNDINGS_BY_ACT);
			ps.setLong(1, activity_id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingItem item = new FundingItem();
//				"SELECT f.id, f.org_id, f.amount, f.harm_currency, f.status, f.comment, f.typeassist, f.termassist, f.signdate " +
//				"FROM funding f, objects o " +
//				"WHERE o.activity_id = ? AND f.id = o.id";
				item.setId(rs.getLong(1));
				item.setOrg(OrganizationDB.getManager(token).getByID(rs.getLong(2)));
				item.setAmount(rs.getDouble(3));
				item.setCurrency(rs.getString(4));
				item.setStatus(PropertyListDB.getManager(db_token).getItemById(rs.getLong(5)));
//				item.setStatus(FundingStatusPersMan.getManager(db_token).getById(rs.getLong(5)));
				item.setComments(rs.getString(6));
				item.setType_of_assistance(PropertyListDB.getManager(db_token).getItemById(rs.getLong(7)));
				item.setTerms_of_assistance(PropertyListDB.getManager(db_token).getItemById(rs.getLong(8)));
				java.sql.Date date = rs.getDate(9);
				if (date != null) {
					item.setSignature_date(date.getTime());
				} else {
					item.setSignature_date(new Date(System.currentTimeMillis()).getTime());
				}

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new FundingItem[items.size()];
			items.toArray(result);
		}

		return result;
	}


	public final void delete(long id)
		 throws SQLException {
		ICFService cfService = db_token.getCFService();
		String poolName = db_token.getPool_name();
		Connection con = cfService.getConnection(poolName);

		deleteTableForItem(id);

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_FND);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	private final void deleteFundingsObjForActivity(long activity_id)
		 throws SQLException {
		ICFService cfService = db_token.getCFService();
		String poolName = db_token.getPool_name();

		Connection con = cfService.getConnection(poolName);

		try {
			PreparedStatement ps = con.prepareStatement("DELETE FROM objects " +
				 "WHERE activity_id=? AND (obj_type=" +
				 AIDAActivityObject.TYPE_FUNDING +
				 " OR obj_type=" +
				 AIDAActivityObject.TYPE_FUNDING_BYCAT +
				 " OR obj_type=" +
				 AIDAActivityObject.TYPE_FUNDING_YEARLY +
				 ")");
			ps.setLong(1, activity_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public final void deleteForActivity(long activity_id)
		 throws SQLException {
		deleteFNDPersForActivity(activity_id);

		FundingItem[] fitems = getFundingsIDSByActivity(activity_id);
		if (fitems != null) {
			for (int i = 0; i < fitems.length; i++) {
				delete(fitems[i].getId());
			}
		}

		deleteFundingsObjForActivity(activity_id);
	}

	private void deleteFNDPersForActivity(long activity_id)
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_FND_PERS);
			ps.setLong(1, activity_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	/**
	 * to be removed ... check the tail of the file
	 *
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Person getPersonByID(long id)
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		Person pers = null;

		try {
			PreparedStatement ps = con.prepareStatement(getPerson);
			ps.setLong(1, id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				pers = new Person();
				pers.setId(id);
				pers.setFirstNames(rs.getString(1));
				pers.setLastName(rs.getString(2));
				pers.setDepartment(rs.getString(3));
				pers.setState(rs.getString(4));
				pers.setAddress(rs.getString(5));
				pers.setPostalCode(rs.getString(6));
				pers.setPhone(rs.getString(7));
				pers.setFax(rs.getString(8));
				pers.setEmail(rs.getString(9));
				pers.setURL(rs.getString(10));
				pers.setCountryISO3(rs.getString(11));
				pers.setUserID(rs.getLong(12));
				pers.setCountryName(rs.getString(13));
				pers.setUpdate(rs.getDate(14));
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return pers;
	}

	public void insertTable(FundingItem item)
		 throws SQLException {
		ArrayList items = item.getDisbursements();
		if (items == null) {
			items = item.getCommitments();
		} else {
			ArrayList commitments = item.getCommitments();
			if (commitments != null) {
				items.addAll(item.getCommitments());
			}
		}

		if (items == null || items.size() < 1) {
			return;
		}

		Connection con = getConnection();
		try {
//			"INSERT INTO fnd3_tables (fnd_item_id, year, amount, currency, date, type) " +
//			"VALUES (?,?, ?,?, ?,?)";
			PreparedStatement ps;
			ps = con.prepareStatement(INSERT_FND3_TABLE_ITEM);
			for (int i = 0; i < items.size(); i++) {
				FundingTableItem titem = (FundingTableItem) items.get(i);
				ps.clearParameters();
				ps.setLong(1, item.getId());
				ps.setInt(2, titem.getYear());
				ps.setDouble(3, titem.getAmount());
				ps.setString(4, titem.getCurrency());
				ps.setDate(5, new Date(titem.getDate()));
				ps.setString(6, titem.getType());
				ps.executeUpdate();
			}
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}
	}

	public void deleteTableForItem(long item_id) throws SQLException {
		Connection con = getConnection();
		try {
//			private static final String DELETE_FND3_TABLE =
//				 "DELETE FROM fnd3_tables WHERE fnd_item_id=?";
			PreparedStatement ps = con.prepareStatement(DELETE_FND3_TABLE);
			ps.setLong(1, item_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}
	}

	public void getTable(FundingItem item) throws SQLException {
		Connection con = getConnection();

		try {
//			private static final String GET_FND3_TABLE =
//				 "SELECT year, amount, currency, date, type " +
//				 "FROM fnd3_tables WHERE fnd_item_id=? ORDER BY type ASC, date ASC";
			PreparedStatement ps = con.prepareStatement(GET_FND3_TABLE);
			ps.setLong(1, item.getId());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				FundingTableItem titem = new FundingTableItem();
				titem.setYear(rs.getInt(1));
				titem.setAmount(rs.getDouble(2));
				titem.setCurrency(rs.getString(3));
				Date date = rs.getDate(4);
				if (date != null) {
					titem.setDate(date.getTime());
				} else {
                         titem.setDate(System.currentTimeMillis());
				}
				titem.setType(rs.getString(5));
				if ("C".equals(titem.getType())) {
					item.getCommitments().add(titem);
				} else if ("D".equals(titem.getType())) {
					item.getDisbursements().add(titem);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}
	}


	private static final String DELETE_FND_PERS =
		 "DELETE FROM funding_pers WHERE activity_id=?";
	private static final String GET_PERS_FOR_ACT =
		 "SELECT funding_id, person_id " +
		 "FROM funding_pers " +
		 "WHERE activity_id=? " +
		 "ORDER BY funding_id ASC";
	private static final String INSERT_PERS =
		 "INSERT INTO funding_pers (funding_id, person_id, activity_id) " +
		 "VALUES (?,?,?)";
	private static final String INSERT_FND_ITEM =
		 "INSERT INTO funding (id, org_id, amount, harm_currency, status, comments, typeassist, termassist, signdate, type) " +
		 "VALUES (?,?,?,?,?,?,?,?,?,-1)";
	private static final String GET_FUNDINGS_BY_ACT =
		 "SELECT f.id, f.org_id, f.amount, f.harm_currency, f.status, f.comments, f.typeassist, f.termassist, f.signdate " +
		 "FROM funding f, objects o " +
		 "WHERE o.activity_id = ? AND f.id = o.id";
	private static final String DELETE_FND =
		 "DELETE FROM funding WHERE id = ?";
	private static final String INSERT_FND_YEARLY =
		 "INSERT INTO funding_yearly (funding_id, category_id, date_start, " +
		 "amount, harm_currency) " +
		 "VALUES (?,?,?,?,?)";
	private static final String INSERT_FND_BY_CAT =
		 "INSERT INTO funding_bycat (funding_id, category_id, amount, harm_currency) " +
		 "VALUES (?,?,?,?)";
	private static final String DELETE_YEARLY_FOR_FND =
		 "DELETE FROM funding_yearly WHERE funding_id=?";
	private static final String DELETE_BYCAT_FOR_FND =
		 "DELETE FROM funding_bycat WHERE funding_id=?";
	private static final String GET_FND_YEARLY =
		 "SELECT category_id, date_start, amount, harm_currency " +
		 "FROM funding_yearly " +
		 "WHERE funding_id=? AND category_id IS NULL";
	private static final String GET_FND_YEARLY_BY_CAT =
		 "SELECT category_id, date_start, amount, harm_currency " +
		 "FROM funding_yearly " +
		 "WHERE funding_id=? AND category_id=?";

	private static final String GET_FND_BYCAT =
		 "SELECT category_id, amount, harm_currency " +
		 "FROM funding_bycat " +
		 "WHERE funding_id = ?";

	private static final String getPerson =
		 "select p.first_names,p.last_name,p.departament,p.state,p.addr1,p.postal_code" +
		 ",p.phone,p.fax,p.email,p.url,p.country_code,p.user_id,c.name,p.updated from persons p , countries_iso3 c where c.iso3=p.country_code and id=?";
	// disbursments and commitments
	private static final String GET_FND3_TABLE =
		 "SELECT year, amount, currency, date, type " +
		 "FROM fnd3_tables WHERE fnd_item_id=? ORDER BY type ASC, date ASC";
	private static final String INSERT_FND3_TABLE_ITEM =
		 "INSERT INTO fnd3_tables (fnd_item_id, year, amount, currency, date, type) " +
		 "VALUES (?,?, ?,?, ?,?)";
	private static final String DELETE_FND3_TABLE =
		 "DELETE FROM fnd3_tables WHERE fnd_item_id=?";
}
